package com.macbulldev.poc.testgin.importer;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.stereotype.Service;

import com.macbulldev.poc.testgin.domain.LiabilityAmount;
import com.macbulldev.poc.testgin.domain.Scenario;
import com.macbulldev.poc.testgin.domain.TransactionQuestion;
import com.macbulldev.poc.testgin.domain.TrialCase;
import com.macbulldev.poc.testgin.domain.TrialGroup;
import com.macbulldev.poc.testgin.domain.Underwriter;

@Service("importBuilder")
@Configurable
public class ImportBuilder 
{
	
	private static final Logger LOGGER = Logger.getLogger(ImportBuilder.class);
	
	public static void generateImportDoc(Scenario scenario,HttpServletResponse response) throws ServletException,IOException
	{	
		
		
		LOGGER.error("Scenario:  "+scenario.getName());
        
        OutputStream os = null;
        
        try {
        	
        	LOGGER.error("Creating Workbook");
        	response.setContentType("application/vnd.ms-excel");
        	response.setHeader("Content-Disposition", "attachment; filename="+scenario.getName()+".xls");
        	
        	os=response.getOutputStream();
        	WritableWorkbook workbook = Workbook.createWorkbook(os);
        	
        	WritableSheet overviewSheet = workbook.createSheet("Overview", 0);
    		WritableSheet testCaseSheet = workbook.createSheet("TestCase", 1);
    		WritableSheet transactionQuestionSheet = workbook.createSheet("TransactionQuestions", 2);
    		WritableSheet policiesSheet = workbook.createSheet("Policies", 3);
    		WritableSheet policyQuestionSheet = workbook.createSheet("PolicyQuestions", 4);
    		WritableSheet endorsementsSheet = workbook.createSheet("Endorsements", 5);
    		WritableSheet endorsementQuestionsSheet = workbook.createSheet("EndorsementQuestions", 6);
    		
    		//Styles
    		WritableFont headerStyle = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
    		WritableCellFormat headerFormat = new WritableCellFormat (headerStyle);
    		//Add Lables
    		Label label = null;
    		
    		//labels for TestCaseSheet
    		label = new Label(0, 0, "TestCaseName");
			testCaseSheet.addCell(label);
			label = new Label(1, 0, "CalculationSteps");
			testCaseSheet.addCell(label);
			label = new Label(2, 0, "VisibilityID");
			testCaseSheet.addCell(label);
			label = new Label(3, 0, "Purpose");
			testCaseSheet.addCell(label);
			label = new Label(4, 0, "SectionID");
			testCaseSheet.addCell(label);
			
			//Add labels for TransactionQuestionSheet
			label = new Label(0, 0, "TestCaseName");
			transactionQuestionSheet.addCell(label);
			label = new Label(1, 0, "QuestionName");
			transactionQuestionSheet.addCell(label);
			label = new Label(2, 0, "AnswerValue");
			transactionQuestionSheet.addCell(label);
			
			//add Labels for PoliciesSheet
			label = new Label(0, 0, "TestCaseName");
			policiesSheet.addCell(label);
			label = new Label(1, 0, "PolicyDecisionPathID");
			policiesSheet.addCell(label);
			label = new Label(2, 0, "ExpectedPremium");
			policiesSheet.addCell(label);
			
			//add Labels for policyQuestionsSheet
			label = new Label(0, 0, "TestCaseName");
			policyQuestionSheet.addCell(label);
			label = new Label(1, 0, "PolicyDecisionPathID");
			policyQuestionSheet.addCell(label);
			label = new Label(2, 0, "QuestionName");
			policyQuestionSheet.addCell(label);
			label = new Label(3, 0, "AnswerValue");
			policyQuestionSheet.addCell(label);
			
			//add Labels for endorsementsSheet
			label = new Label(0, 0, "TestCaseName");
			endorsementsSheet.addCell(label);
			label = new Label(1, 0, "PolicyDecisionPathID");
			endorsementsSheet.addCell(label);
			label = new Label(2, 0, "MappedEndorsementName");
			endorsementsSheet.addCell(label);
			label = new Label(3, 0, "ExpectedEndorsementAmount");
			endorsementsSheet.addCell(label);
			
			//add Labels for endorsementQuestionSheet
			label = new Label(0, 0, "TestCaseName");
			endorsementQuestionsSheet.addCell(label);
			label = new Label(1, 0, "PolicyDecisionPathID");
			endorsementQuestionsSheet.addCell(label);
			label = new Label(2, 0, "MappedEndorsementName");
			endorsementQuestionsSheet.addCell(label);
			label = new Label(3, 0, "QuestionName");
			endorsementQuestionsSheet.addCell(label);
			label = new Label(4, 0, "AnswerValue");
			endorsementQuestionsSheet.addCell(label);
			
			overviewSheet = writeOverviewSheet(overviewSheet,scenario,headerFormat);
			testCaseSheet = writeTestCaseSheet(testCaseSheet,scenario);
			transactionQuestionSheet = writeTransactionQuestionSheet(transactionQuestionSheet,scenario);
			policiesSheet = writePoliciesSheet(policiesSheet,scenario);
			policyQuestionSheet = writePolicyQuestionSheet(policyQuestionSheet,scenario);
			
			
    		workbook.write();
    		workbook.close();
        }
        catch (Exception e){
        	   throw new ServletException("Exception in Excel Sample Servlet", e);
        } 
        finally{
        	try
            {
                  os.flush();
            }
            catch (Exception e)
            { LOGGER.error("Failure to Flush OutputStreem:  ",e);}
            try
            {
                  os.close();
            }
            catch (Exception e)
            {LOGGER.error("Failure to Close OutputStreem:  ",e);}
        }//End Finally
		

	}
	
	public static WritableSheet writeOverviewSheet(WritableSheet overviewSheet, Scenario scenario, WritableCellFormat headerFormat)
	{
		
		//Flesh Out Overview
		
		try{
		
		Label label = null;
		overviewSheet.setColumnView(0, 30);
		label = new Label(0, 0, "Overview",headerFormat);
		overviewSheet.addCell(label);
		label = new Label(0, 2, "State",headerFormat);
		overviewSheet.addCell(label);
		label = new Label(1, 2, scenario.getStateOrProvince().getDisplayName());
		overviewSheet.addCell(label);
		label = new Label(0, 3, "Name",headerFormat);
		overviewSheet.addCell(label);
		label = new Label(1, 3, scenario.getName());
		overviewSheet.addCell(label);
		label = new Label(0, 4, "Created By",headerFormat);
		overviewSheet.addCell(label);
		label = new Label(1, 4, scenario.getCreateUsername());
		overviewSheet.addCell(label);
		label = new Label(0, 5, "Create Ts",headerFormat);
		overviewSheet.addCell(label);
		label = new Label(1, 5, scenario.getCreateTs().toString());
		overviewSheet.addCell(label);
		label = new Label(0, 6, "Last Updated By",headerFormat);
		overviewSheet.addCell(label);
		label = new Label(1, 6, scenario.getUpdateUsername());
		overviewSheet.addCell(label);
		label = new Label(0, 7, "Update Ts",headerFormat);
		overviewSheet.addCell(label);
		label = new Label(1, 7, scenario.getUpdateTs().toString());
		overviewSheet.addCell(label);
		
		label = new Label(0, 9, "Underwriters",headerFormat);
		overviewSheet.addCell(label);
		
		int rowCnt = 9;
		for(Underwriter uw : scenario.getUnderwriters())
		{
			label = new Label(1, rowCnt, uw.getDisplayName());
			overviewSheet.addCell(label);
			rowCnt++;
		}
		
		/*
		rowCnt++;
		label = new Label(0, rowCnt++, "Policy Forms",headerFormat);
		overviewSheet.addCell(label);
		
		rowCnt--;
		for(PolicyForm pf : scenario.getPolicyForms())
		{
			label = new Label(1, rowCnt, pf.getDisplayName());
			overviewSheet.addCell(label);
			rowCnt++;
		}
		*/
		
		rowCnt++;
		rowCnt++;
		label = new Label(0, rowCnt++, "PolicyType",headerFormat);
		overviewSheet.addCell(label);
		
		rowCnt--;
		label = new Label(1, rowCnt, scenario.getPolicyType().getDisplayName());
		overviewSheet.addCell(label);
		
		rowCnt++;
		label = new Label(0, rowCnt++, "# of Test Groups",headerFormat);
		overviewSheet.addCell(label);
		rowCnt--;
		label = new Label(1, rowCnt, scenario.getTrialGroups().size()+"");
		overviewSheet.addCell(label);
		
		}
		catch(Exception ex)
		{
			LOGGER.error("problem filling out overview:  ",ex);
		}
		
		return overviewSheet;
	}
	
	public static WritableSheet writeTestCaseSheet(WritableSheet testCaseSheet, Scenario scenario)
	{
		
		try{
			Label label = null;
			int rowCnt = 0;
			
			testCaseSheet.setColumnView(0, 100);
			testCaseSheet.setColumnView(1, 100);
			testCaseSheet.setColumnView(2, 100);
			
			
			for(TrialGroup tg : scenario.getTrialGroups())
			{
				for(TrialCase tc : tg.getTrialCases())
				{
					rowCnt++;
					label = new Label(0, rowCnt, tc.getName());
					testCaseSheet.addCell(label);
					label = new Label(1, rowCnt, tc.getCalcSteps());
					testCaseSheet.addCell(label);
					
					if(tc.getTrialGroup().getVisibility().equalsIgnoreCase("public"))
					{
						label = new Label(2, rowCnt, "1");
						testCaseSheet.addCell(label);
					}
					else
					{
						label = new Label(2, rowCnt, "2");
						testCaseSheet.addCell(label);
					}
					
					label = new Label(3, rowCnt, "To Test the The "+scenario.getPolicyType().getDisplayName()+" for "+scenario.getStateOrProvince().getDisplayName()+" are calculated properly");
					testCaseSheet.addCell(label);
				}
			}
			
		}
		catch(Exception ex)
		{
			
		}
		return testCaseSheet;
	}
	
	public static WritableSheet writeTransactionQuestionSheet(WritableSheet transactionQuestionSheet, Scenario scenario)
	{
		
		try{
			Label label = null;
			int rowCnt = 0;
			
			transactionQuestionSheet.setColumnView(0, 100);
			transactionQuestionSheet.setColumnView(1, 100);
			transactionQuestionSheet.setColumnView(2, 100);
			
			
			for(TrialGroup tg : scenario.getTrialGroups())
			{
				for(TrialCase tc : tg.getTrialCases())
				{
					rowCnt++;
					
					//underwriter row
					label = new Label(0, rowCnt, tc.getName());
					transactionQuestionSheet.addCell(label);
					
					String underwriter = "";
					for(Underwriter uw: Underwriter.findAllUnderwriters())
					{
						if(tc.getName().contains(uw.getImportName()))
						{
							underwriter = uw.getImportName();
						}
					}
					label = new Label(1, rowCnt, "Underwriter");
					transactionQuestionSheet.addCell(label);
					label = new Label(2, rowCnt, underwriter);
					transactionQuestionSheet.addCell(label);

					//liability Amounts
					rowCnt++;
					label = new Label(0, rowCnt, tc.getName());
					transactionQuestionSheet.addCell(label);
					for(LiabilityAmount la: tc.getLiabilityAmounts())
					{
						label = new Label(1, rowCnt, la.getImportName());
						transactionQuestionSheet.addCell(label);
						
						label = new Label(2, rowCnt, la.getAmount().toPlainString());
						transactionQuestionSheet.addCell(label);
					}
					
					//policy effective date
					rowCnt++;
					label = new Label(0, rowCnt, tc.getName());
					transactionQuestionSheet.addCell(label);
					
					label = new Label(1, rowCnt, "PolicyEffectiveDate");
					transactionQuestionSheet.addCell(label);
					
					SimpleDateFormat sdf =  new SimpleDateFormat  (  "MM/dd/yyyy"  ) ;
					label = new Label(2, rowCnt, sdf.format(tc.getTrialGroup().getPolicyEffectiveDate()));
					transactionQuestionSheet.addCell(label);
					
					//all transaction questions
					for(TransactionQuestion tq: tc.getTrialGroup().getTransactionQuestions())
					{
						rowCnt++;
						label = new Label(0, rowCnt, tc.getName());
						transactionQuestionSheet.addCell(label);
						label = new Label(1, rowCnt, tq.getQuestion());
						transactionQuestionSheet.addCell(label);
						label = new Label(2, rowCnt, tq.getAnswer());
						transactionQuestionSheet.addCell(label);
					}
					
				}
			}
			
		}
		catch(Exception ex)
		{
			LOGGER.error("Error Writing Out Transaction Question Sheet: ",ex);
		}
		return transactionQuestionSheet;
	}
	
	public static WritableSheet writePoliciesSheet(WritableSheet policiesSheet, Scenario scenario)
	{
		
		try{
			Label label = null;
			int rowCnt = 0;
			
			policiesSheet.setColumnView(0, 100);
			policiesSheet.setColumnView(1, 100);
			policiesSheet.setColumnView(2, 100);
			
			for(TrialGroup tg : scenario.getTrialGroups())
			{
				for(TrialCase tc : tg.getTrialCases())
				{
					rowCnt++;

					label = new Label(0, rowCnt, tc.getName());
					policiesSheet.addCell(label);
					
					int laCnt = 0;
					String decisionPath = "No Decision Path Found for Test Case";
					
					LOGGER.error("This is the policy Type:  "+scenario.getPolicyType().getDisplayName()+"  -   "+scenario.getPolicyType().getAbbrName());
					
					for(LiabilityAmount la: tc.getLiabilityAmounts())
					{
						laCnt++;

						if(laCnt == 1)
						{
							decisionPath = "11";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownBasic") == true)
						{
							decisionPath = "10";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownBasicReiss") == true)
						{
							decisionPath = "10";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownFrcls") == true)
						{
							decisionPath = "10";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownFrclsReiss") == true)
						{
							decisionPath = "10";
						}
						
						if(laCnt == 2)
						{
							decisionPath = "12";
						}
						else if(laCnt == 3)
						{
							decisionPath = "13";
						}
						else if(laCnt == 4)
						{
							decisionPath = "14";
						}
						else if(laCnt == 5)
						{
							decisionPath = "15";
						}

						
						label = new Label(1, rowCnt, decisionPath);
						policiesSheet.addCell(label);
						label = new Label(2, rowCnt, la.getPremium().toPlainString());
						policiesSheet.addCell(label);
					}
					
					
					
					//label = new Label(1, rowCnt, tc.getTrialGroup().getDecisionPath().toString());
					//policiesSheet.addCell(label);
					//label = new Label(2, rowCnt, tc.getPremium().toPlainString());
					//policiesSheet.addCell(label);
				}	
			}
			
		}
		catch(Exception ex)
		{
			LOGGER.error("Error Writing Policies Sheet:  ",ex);
		}
		
		return policiesSheet;
		
	}
	
	public static WritableSheet writePolicyQuestionSheet(WritableSheet policyQuestionSheet, Scenario scenario)
	{
		
		try{
			Label label = null;
			int rowCnt = 0;
			
			policyQuestionSheet.setColumnView(0, 100);
			policyQuestionSheet.setColumnView(1, 30);
			policyQuestionSheet.setColumnView(2, 30);
			policyQuestionSheet.setColumnView(3, 50);
			
			for(TrialGroup tg : scenario.getTrialGroups())
			{
				for(TrialCase tc : tg.getTrialCases())
				{
					rowCnt++;

					label = new Label(0, rowCnt, tc.getName());
					policyQuestionSheet.addCell(label);
					
					
					//adding the decision path to the policies
					
					int laCnt = 0;
					String decisionPath = "";
					
					for(LiabilityAmount la: tc.getLiabilityAmounts())
					{
						laCnt++;

						if(laCnt == 1)
						{
							decisionPath = "11";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownBasic") == true)
						{
							decisionPath = "10";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownBasicReiss") == true)
						{
							decisionPath = "10";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownFrcls") == true)
						{
							decisionPath = "10";
						}
						if(laCnt == 1 && scenario.getPolicyType().getAbbrName().equalsIgnoreCase("ownFrclsReiss") == true)
						{
							decisionPath = "10";
						}
						
						if(laCnt == 2)
						{
							decisionPath = "12";
						}
						else if(laCnt == 3)
						{
							decisionPath = "13";
						}
						else if(laCnt == 4)
						{
							decisionPath = "14";
						}
						else if(laCnt == 5)
						{
							decisionPath = "15";
						}
						
						label = new Label(1, rowCnt, decisionPath);
						policyQuestionSheet.addCell(label);
						
						label = new Label(2, rowCnt, "PolicyForm");
						policyQuestionSheet.addCell(label);
						label = new Label(3, rowCnt, la.getPolicyForm().getImportName());
						policyQuestionSheet.addCell(label);
					}
				}
			}
			
		}
		catch(Exception ex)
		{
			LOGGER.error("Error Writing Policy Question Sheet:  ",ex);
		}
		
		return policyQuestionSheet;
		
	}

}
